﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//using log4net;
using IDML.BusinessObject;

namespace IDML.DataAccess
{
    public class TestDAL
    {
        private DBConnection _dbConnect;

        public TestDAL()
        {
            _dbConnect = new DBConnection();
        }

        #region PopulateLookups

        public List<TestCategoryBO> PopulateTestCategoryLookup()
        {
            SqlConnection connection = null;
            DataTable dt = null;
            TestCategoryBO testCategory = null;
            List<TestCategoryBO> testcategoryList = null;

            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();
                SqlCommand selectCommand = new SqlCommand("GetTestCategory_List", connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da = new SqlDataAdapter(selectCommand);
                dt = new DataTable();
                _dbConnect.CommitTransaction();
                da.Fill(dt);
                _dbConnect.CloseConnection();

                testcategoryList = new List<TestCategoryBO>();

                foreach (DataRow row in dt.Rows)
                {
                    testCategory = new TestCategoryBO();
                    testCategory.TestCategoryID = Convert.ToInt32(row[0]);
                    testCategory.TestCategory = row[1].ToString();
                    testcategoryList.Add(testCategory);
                }

                return testcategoryList;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public List<TestBO> PopulateTestLookup(int testCategoryID)
        {
            SqlConnection connection = null;
            DataTable dt = null;
            TestBO test = null;
            List<TestBO> testList = null;

            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();
                SqlCommand selectCommand = new SqlCommand("GetTest_List", connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.Parameters.Add("@TestCategoryId", SqlDbType.Int).Value = testCategoryID;
                SqlDataAdapter da = new SqlDataAdapter(selectCommand);
                dt = new DataTable();
                _dbConnect.CommitTransaction();
                da.Fill(dt);
                _dbConnect.CloseConnection();

                testList = new List<TestBO>();

                foreach (DataRow row in dt.Rows)
                {
                    test = new TestBO();
                    test.TestId = Convert.ToInt32(row[0]);
                    test.TestName = row[1].ToString();
                    testList.Add(test);
                }

                return testList;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Select Statements

        public TestStructureBO TestStructure_Select(TestStructureBO testStructureBO)
        {
            SqlConnection connection = null;
            DataSet ds = null;
            TestStructureBO testStructure = null;         
            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();
                SqlCommand selectCommand = new SqlCommand("TestStructure_Select", connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.Parameters.Add("@TestStructureId", SqlDbType.Int).Value = testStructureBO.TestStructureId;
                SqlDataAdapter da = new SqlDataAdapter(selectCommand);
                ds = new DataSet();
                _dbConnect.CommitTransaction();
                da.Fill(ds);
                _dbConnect.CloseConnection();
               
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    testStructure = new TestStructureBO();
                    testStructure.TestStructureId = int.Parse(row["TestStructureId"].ToString());
                    testStructure.TestId = int.Parse(row["TestId"].ToString());
                    testStructure.TestStructureText = row["TestStructureText"].ToString();
                    testStructure.Unit = row["Unit"].ToString();
                    testStructure.TextOrder = int.Parse(row["TextOrder"].ToString());
                    testStructure.ControlId = int.Parse(row["ControlId"].ToString());
                    testStructure.DependTestStructureId = row["DependTestStructureId"].ToString() == "" ? 0 : int.Parse(row["DependTestStructureId"].ToString());
                    testStructure.Operator = row["Operator"].ToString();
                    testStructure.ValidationId = int.Parse(row["ValidationId"].ToString());
                }
                return testStructure;
            }
            catch (Exception ex)
            {
                string error = ex.Message;
                //Log.Error("GetUser", ex);
                _dbConnect.RollbackTransaction();
                _dbConnect.CloseConnection();
            }
            finally
            {
                _dbConnect.CloseConnection();
            }
            return testStructure;
        }
        
        public List<TestStructureBO> GetTestStructure_Listst(TestBO testBO)
        {
            SqlConnection connection = null;
            DataSet ds = null;
            TestStructureBO testStructure = null;
            List<TestStructureBO> reportTextList = null;
            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();
                SqlCommand selectCommand = new SqlCommand("GetTestStructure_List", connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.Parameters.Add("@TestId", SqlDbType.Int).Value = testBO.TestId;
                SqlDataAdapter da = new SqlDataAdapter(selectCommand);
                ds = new DataSet();
                _dbConnect.CommitTransaction();
                da.Fill(ds);
                _dbConnect.CloseConnection();

                reportTextList = new List<TestStructureBO>();
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    testStructure = new TestStructureBO();
                    testStructure.TestStructureId = int.Parse(row["TestStructureId"].ToString());
                    testStructure.TestId = int.Parse(row["TestId"].ToString());
                    testStructure.TestStructureText = row["TestStructureText"].ToString();
                    testStructure.Unit = row["Unit"].ToString();
                    testStructure.TextOrder = int.Parse(row["TextOrder"].ToString());
                    testStructure.ControlId = int.Parse(row["ControlId"].ToString());
                    testStructure.DependTestStructureId = row["DependTestStructureId"].ToString() == "" ? 0 : int.Parse(row["DependTestStructureId"].ToString());
                    testStructure.Operator = row["Operator"].ToString();
                    reportTextList.Add(testStructure);
                }
                return reportTextList;
            }
            catch (Exception ex)
            {
                string error = ex.Message;
                //Log.Error("GetUser", ex);
                _dbConnect.RollbackTransaction();
                _dbConnect.CloseConnection();
            }
            finally
            {
                _dbConnect.CloseConnection();
            }
            return reportTextList;
        }

        public List<MasterListDataBO> GetMasterListData(MasterListDataBO masterListDataBO)
        {
            SqlConnection connection = null;
            DataSet ds = null;
            MasterListDataBO masterListData = null;
            List<MasterListDataBO> masterListDatatList = null;
            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();
                SqlCommand selectCommand = new SqlCommand("GetMasterListData", connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.Parameters.Add("@TestStructureId", SqlDbType.Int).Value = masterListDataBO.TestStructureId;
                SqlDataAdapter da = new SqlDataAdapter(selectCommand);
                ds = new DataSet();
                _dbConnect.CommitTransaction();
                da.Fill(ds);
                _dbConnect.CloseConnection();

                masterListDatatList = new List<MasterListDataBO>();
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    masterListData = new MasterListDataBO();
                    masterListData.MasterListDataId = int.Parse(row["MasterListDataId"].ToString());
                    masterListData.TestStructureId = int.Parse(row["TestStructureId"].ToString());
                    masterListData.Value = row["Value"].ToString();

                    masterListDatatList.Add(masterListData);
                }
                return masterListDatatList;
            }
            catch (Exception ex)
            {
                string error = ex.Message;
                //Log.Error("GetUser", ex);
                _dbConnect.RollbackTransaction();
                _dbConnect.CloseConnection();
            }
            finally
            {
                _dbConnect.CloseConnection();
            }
            return masterListDatatList;
        }

        public List<TestFeeStructDetailsBO> GetTestFeeStructure(TestBO testBO)
        {
            SqlConnection connection = null;
            DataSet ds = null;
            TestFeeStructDetailsBO testfeestructureDet = null;
            List<TestFeeStructDetailsBO> testfeestructureDetList = null;

            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();
                SqlCommand selectCommand = new SqlCommand("GetTestFeeStructure", connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.Parameters.Add("@TestCode", SqlDbType.NVarChar).Value = testBO.TestCode;
                SqlDataAdapter da = new SqlDataAdapter(selectCommand);
                ds = new DataSet();
                _dbConnect.CommitTransaction();
                da.Fill(ds);
                _dbConnect.CloseConnection();

                testfeestructureDet = new TestFeeStructDetailsBO();
                testfeestructureDetList = new List<TestFeeStructDetailsBO>();

                testfeestructureDet.TestId = int.Parse(ds.Tables[0].Rows[0]["TestId"].ToString());
                testfeestructureDet.TestFeeStructureId = int.Parse(ds.Tables[0].Rows[0]["TestFeeStructureId"].ToString());
                testfeestructureDet.TestCode = ds.Tables[0].Rows[0]["TestCode"].ToString();
                testfeestructureDet.TestName = ds.Tables[0].Rows[0]["TestName"].ToString();
                testfeestructureDet.Amount = Convert.ToDecimal(ds.Tables[0].Rows[0]["Amount"].ToString());

                testfeestructureDetList.Add(testfeestructureDet);

            }
            catch (Exception ex)
            {
                string error = ex.Message;
                _dbConnect.RollbackTransaction();
                _dbConnect.CloseConnection();
            }
            finally
            {
                _dbConnect.CloseConnection();
            }

            return testfeestructureDetList;
        }

        public List<TestFeeStructDetailsBO> GetTestFeeStructurebyTestID(int testID)
        {
            SqlConnection connection = null;
            DataTable dt = null;
            TestFeeStructDetailsBO testfeestructureDet = null;
            List<TestFeeStructDetailsBO> testfeestructureDetList = null;

            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();
                SqlCommand selectCommand = new SqlCommand("GetTestFeeStructurebyTestID", connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.Parameters.Add("@TestID", SqlDbType.Int).Value = testID;
                SqlDataAdapter da = new SqlDataAdapter(selectCommand);
                dt = new DataTable();
                _dbConnect.CommitTransaction();
                da.Fill(dt);
                _dbConnect.CloseConnection();

                testfeestructureDet = new TestFeeStructDetailsBO();
                testfeestructureDetList = new List<TestFeeStructDetailsBO>();
                if (dt.Rows.Count != 0)
                {
                    testfeestructureDet.TestId = int.Parse(dt.Rows[0]["TestId"].ToString());
                    testfeestructureDet.TestFeeStructureId = int.Parse(dt.Rows[0]["TestFeeStructureId"].ToString());
                    testfeestructureDet.TestCode = dt.Rows[0]["TestCode"].ToString();
                    testfeestructureDet.TestName = dt.Rows[0]["TestName"].ToString();
                    testfeestructureDet.Amount = Convert.ToDecimal(dt.Rows[0]["Amount"].ToString());
                }

                testfeestructureDetList.Add(testfeestructureDet);
                return testfeestructureDetList;

            }
            catch (Exception ex)
            {
                string error = ex.Message;
                _dbConnect.RollbackTransaction();
                _dbConnect.CloseConnection();
            }
            finally
            {
                _dbConnect.CloseConnection();
            }

            return testfeestructureDetList;
        }

        public List<ValidationBO> GetValidationData(ValidationBO validationBo)
        {
            SqlConnection connection = null;
            DataSet ds = null;
            ValidationBO validation = null;
            List<ValidationBO> validationDatatList = null;
            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();
                SqlCommand selectCommand = new SqlCommand("GetValidation", connection);
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.Parameters.Add("@TestId", SqlDbType.Int).Value = validationBo.TestId;
                SqlDataAdapter da = new SqlDataAdapter(selectCommand);
                ds = new DataSet();
                _dbConnect.CommitTransaction();
                da.Fill(ds);
                _dbConnect.CloseConnection();

                validationDatatList = new List<ValidationBO>();
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    validation = new ValidationBO();
                    validation.TestId = int.Parse(row["TestId"].ToString());
                    validation.TestStructureId = int.Parse(row["TestStructureId"].ToString());
                    validation.ValidationId = int.Parse(row["ValidationId"].ToString());
                    validation.ValidationTypeId = int.Parse(row["ValidationTypeId"].ToString());
                    validation.Message = row["Message"].ToString();
                    validation.Expression = row["Expression"].ToString();
                    validationDatatList.Add(validation);
                }
                return validationDatatList;
            }
            catch (Exception ex)
            {
                string error = ex.Message;
                //Log.Error("GetUser", ex);
                _dbConnect.RollbackTransaction();
                _dbConnect.CloseConnection();
            }
            finally
            {
                _dbConnect.CloseConnection();
            }
            return validationDatatList;
        }

        #endregion

        #region Insert / Update Statements

        public int PatientTestResult_Insert(PatientTestResultBO patientTestResultBo)
        {
            SqlConnection connection = null;

            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();
                SqlCommand insertcommand = new SqlCommand("PatientTestResult_Insert", connection);
                insertcommand.CommandType = CommandType.StoredProcedure;
                insertcommand.Parameters.Add("@PatientId", SqlDbType.Int).Value = patientTestResultBo.PatientId;
                insertcommand.Parameters.Add("@TestStructureId", SqlDbType.Int).Value = patientTestResultBo.TestStructureId;
                insertcommand.Parameters.Add("@Value", SqlDbType.NVarChar).Value = patientTestResultBo.Value;

                _dbConnect.CommitTransaction();
                int patid = Convert.ToInt32(insertcommand.ExecuteScalar());
                _dbConnect.CloseConnection();
                return patid;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                _dbConnect.CloseConnection();
            }

        }

        public void TestFeeStructure_Save(TestFeeStructureBO testfeestructBO)
        {
            SqlConnection connection = null;


            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();
                SqlCommand insertcommand = new SqlCommand("TestFeeStructure_Save", connection);
                insertcommand.CommandType = CommandType.StoredProcedure;
                insertcommand.Parameters.Add("@TestId", SqlDbType.Int).Value = testfeestructBO.TestId;
                insertcommand.Parameters.Add("@Amount", SqlDbType.Int).Value = testfeestructBO.Amount;
                insertcommand.Parameters.Add("@IUserID", SqlDbType.Int).Value = testfeestructBO.IUserID;
                insertcommand.Parameters.Add("@UUserID", SqlDbType.Int).Value = testfeestructBO.UUserID;

                _dbConnect.CommitTransaction();
                insertcommand.ExecuteScalar();
                _dbConnect.CloseConnection();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                _dbConnect.CloseConnection();
            }
        }

        public int TestStructure_Insert(TestStructureBO testStructure, DataTable masterListData)
        {
            SqlConnection connection = null;

            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();
                SqlCommand insertcommand = new SqlCommand("TestStructure_Insert", connection);
                insertcommand.CommandType = CommandType.StoredProcedure;
                insertcommand.Parameters.Add("@TestId", SqlDbType.Int).Value = testStructure.TestId;
                insertcommand.Parameters.Add("@TestStructureText", SqlDbType.NVarChar).Value = testStructure.TestStructureText;
                insertcommand.Parameters.Add("@Unit", SqlDbType.NVarChar).Value = testStructure.Unit;
                insertcommand.Parameters.Add("@ControlId", SqlDbType.Int).Value = testStructure.ControlId;
                insertcommand.Parameters.Add("@DependTestStructureId", SqlDbType.Int).Value = testStructure.DependTestStructureId;
                insertcommand.Parameters.Add("@Operator", SqlDbType.NVarChar).Value = testStructure.Operator;
                insertcommand.Parameters.Add("@ValidationId", SqlDbType.Int).Value = testStructure.ValidationId;
                insertcommand.Parameters.Add("@TextOrder", SqlDbType.Int).Value = testStructure.TextOrder;

                _dbConnect.CommitTransaction();
                int testStructureid = Convert.ToInt32(insertcommand.ExecuteScalar());
                // int testStructureid = insertcommand.ExecuteNonQuery();                                             
                 _dbConnect.CloseConnection();

                 if (masterListData != null)
                 {
                     foreach (DataRow dr in masterListData.Rows)
                     {                     
                     connection = _dbConnect.OpenConnection();
                     _dbConnect.BeginTransaction();
                     SqlCommand mcommand = new SqlCommand("MasterListData_Insert", connection);
                     mcommand.CommandType = CommandType.StoredProcedure;
                     mcommand.Parameters.Add("@TestStructureId", SqlDbType.Int).Value = testStructureid;
                     mcommand.Parameters.Add("@ID", SqlDbType.Int).Value = (int)dr.ItemArray[0];
                     mcommand.Parameters.Add("@Value", SqlDbType.NVarChar).Value = dr.ItemArray[1].ToString();
                     mcommand.ExecuteNonQuery();
                     _dbConnect.CommitTransaction();
                     _dbConnect.CloseConnection();
                     }
                 }

                return testStructureid;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                _dbConnect.CloseConnection();
            }
        }


        public void TestStructure_Update(TestStructureBO testStructure)
        {
            SqlConnection connection = null;

            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();
                SqlCommand insertcommand = new SqlCommand("TestStructure_Update", connection);
                insertcommand.CommandType = CommandType.StoredProcedure;

                insertcommand.Parameters.Add("@TestStructureId", SqlDbType.Int).Value = testStructure.TestStructureId;
                insertcommand.Parameters.Add("@TestId", SqlDbType.Int).Value = testStructure.TestId;
                insertcommand.Parameters.Add("@TestStructureText", SqlDbType.NVarChar).Value = testStructure.TestStructureText;
                insertcommand.Parameters.Add("@Unit", SqlDbType.NVarChar).Value = testStructure.Unit;
                insertcommand.Parameters.Add("@ControlId", SqlDbType.Int).Value = testStructure.ControlId;
                insertcommand.Parameters.Add("@DependTestStructureId", SqlDbType.Int).Value = testStructure.DependTestStructureId;
                insertcommand.Parameters.Add("@Operator", SqlDbType.NVarChar).Value = testStructure.Operator;
                insertcommand.Parameters.Add("@ValidationId", SqlDbType.Int).Value = testStructure.ValidationId;
                insertcommand.Parameters.Add("@TextOrder", SqlDbType.Int).Value = testStructure.TextOrder;

                _dbConnect.CommitTransaction();
               insertcommand.ExecuteNonQuery();
                // int testStructureid = insertcommand.ExecuteNonQuery();                                             
                _dbConnect.CloseConnection();
                            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                _dbConnect.CloseConnection();
            }
        }
     




        #endregion

        #region Delete Statements
        public int TestStructure_Delete(TestStructureBO testStructureBO)
        {
            SqlConnection connection = null;

            try
            {
                connection = _dbConnect.OpenConnection();
                _dbConnect.BeginTransaction();
                SqlCommand deletecommand = new SqlCommand("TestStructure_Delete", connection);
                deletecommand.CommandType = CommandType.StoredProcedure;
                deletecommand.Parameters.Add("@TestStructureId", SqlDbType.Int).Value = testStructureBO.TestStructureId;               
                _dbConnect.CommitTransaction();
                int success = Convert.ToInt32(deletecommand.ExecuteScalar());
                _dbConnect.CloseConnection();
                return success;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                _dbConnect.CloseConnection();
            }

        }
        #endregion

    }
}
